Create Staging Tables in Staging Database and Populate the Staging Tables 8

Download the Sql File attached to the  

Run Script that is attached to create your Staging tables in the Staging Database. Once ran the tables will be made up of these columns.



Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard;

To Load Stg_ReqLocation Table:



Source ReqA:


Source2 Agri Labor Reqs Table:



Sort Transformation:



Sort1:



Merge Join:



Derived Column:


Code:

InsertDate          <add as new column>    GETDATE()          database timestamp [DT_DBTIMESTAMP]           

UpdateDate       <add as new column>    GETDATE()          database timestamp [DT_DBTIMESTAMP]           

RecordFlag          <add as new column>    1                              four-byte signed integer [DT_I4]

Personnel Subarea Code          Replace 'Personnel Subarea Code'                ISNULL([Personnel Subarea Code]) ? "Unknown" : [Personnel Subarea Code]                 Unicode string [DT_WSTR]                           255

Personnel Area Code                      Replace 'Personnel Area Code'                   ISNULL([Personnel Area Code]) ? "Unknown" : [Personnel Area Code]                  Unicode string [DT_WSTR]                           255       

Address 1                            Replace 'Address 1'                          ISNULL([Address 1]) ? "Unknown" : [Address 1]                  Unicode string [DT_WSTR]                           255        

City                        Replace 'City'                      ISNULL(City) ? "Unknown" : City                 Unicode string [DT_WSTR]                           255

State/Province Personnel Subarea Abbreviation                Replace 'State/Province Personnel Subarea Abbreviation'                                ISNULL([State/Province Personnel Subarea Abbreviation]) ? "Unknown" : [State/Province Personnel Subarea Abbreviation]                   Unicode string [DT_WSTR]                           255

ZIP/Postal Code                Replace 'ZIP/Postal Code'                             ISNULL([ZIP/Postal Code]) ? "Unknown" : [ZIP/Postal Code]                    Unicode string [DT_WSTR]                           255         

Personnel Area                 Replace 'Personnel Area'                              ISNULL([Personnel Area]) ? "Unkown" : [Personnel Area]                     Unicode string [DT_WSTR]                           255

Address 2                            Replace 'Address 2'                          ISNULL([Address 2]) ? "Unknown" : [Address 2]                  Unicode string [DT_WSTR]                           255


Conditional Split:


Code:

1 RemoveEmpty LEN([Address 2]) == 0


Derived Column1 :

Code:

Address 2 Replace 'Address 2' "Unknown" Unicode string [DT_WSTR] 255



Union All:


OLEDB Destination:


Mappings:




Repeat the same steps to load all the staging tables. 

To Load Stg_BusinessCode Table:


Source: Alt_Spend Table and choose all columns



Aggregate Transformation:


Derived Column Transformation:


Conditional split:


Derived Column:


Union all:



Destination: Stg_BusinessCode:


Mappings:




To Load Stg_ReqDistributor Table:


Source Vw_ReqA:

SQL command Text:

SELECT [ReqA_Requisition ID], [ReqA_Close Comments], [ReqA_Close Reason], [ReqA_Distribution List],
 [ReqA_# Cancelled]
FROM [dbo].[vw_ReqA]

Source Timing:

SQL command Text:

select distinct  [Job
Posting ID],F8, [Supplier Response],[Selection Process]
 FROM [dbo].[Timing]

Source Resumes:

SQL command Text:

SELECT DISTINCT [Job Posting ID],[Candidate ID], [Security
ID],
 Candidate, [Candidate
First Submit Date], [Candidate Last Interview Date], [Candidate Reject Date],
[Candidate Shortlist Date], 
 [Candidate Status],
[Candidate Submit Date], [Quality Rating], [Reason:], [# Shortlisted]
 FROM [dbo].[Resumes]
 where [Job Posting
ID] is not null

Sort:


Sort 1:


Merge Join:



Sort2:


Sort 3:



Merge Join 1:



Data Conversion:


Derived Column 2 Transformation:

Derived Column 2 code:

GETDATE()

GETDATE()

ISNULL([Job Posting ID]) ? "Unknown" : [Job Posting ID]

ISNULL(F8) ? "Unknown" : F8

ISNULL([ReqA_# Cancelled]) ? 0 : [ReqA_# Cancelled]

ISNULL([ReqA_Close Reason]) ? "Unknown" : [ReqA_Close Reason]

ISNULL([ReqA_Close Comments]) ? "Unknown" : [ReqA_Close Comments]

ISNULL([ReqA_Distribution List]) ? "Unknown" : [ReqA_Distribution List]

ISNULL([Security ID]) ? "Unknown" : [Security ID]

ISNULL([Candidate ID]) ? "Unknown" : [Candidate ID]

ISNULL(Candidate) ? "Unknown" : Candidate

ISNULL([Candidate Status]) ? "Unknown" : [Candidate Status]

ISNULL([Copy of Candidate Submit Date]) ? "Unknown" : [Copy of Candidate Submit Date]

ISNULL([Copy of Candidate Reject Date]) ? "Unknown" : [Copy of Candidate Reject Date]

ISNULL(Reason) ? "Unknown" : Reason

ISNULL([Copy of Candidate Last Interview Date]) ? "Unknown" : [Copy of Candidate Last Interview Date]

ISNULL([Copy of Candidate First Submit Date]) ? " Unknown " : [Copy of Candidate First Submit Date]

ISNULL([Copy of Candidate Shortlist Date]) ? "Unknown" : [Copy of Candidate Shortlist Date]

ISNULL([Copy of # Shortlisted]) ? "0" : [Copy of # Shortlisted]

ISNULL([Supplier Response]) ? 0 : [Supplier Response]

ISNULL([Quality Rating]) ? "Unknown" : [Quality Rating]

ISNULL([Selection Process]) ? 0 : [Selection Process]

Conditional Split Transformation:


Derived Column 1:


Union All Transformation:





OLEDB Destination:



Mappings:



To Load Stg_Headcount (Full Mth) Table:




SQL Command text:

WITH CTE AS
 (
 SELECT  ROW_NUMBER() OVER (PARTITION BY
[Headcount_Work Order ID], [Worker ID_HeadCount]
  ORDER BY[Headcount_Month]
desc ) AS RankWorkOrderID, *
 FROM
[dbo].[vw_HeadCount_FullMth] )
 SELECT [Headcount_Assignment
Length], [Headcount_Diverse Supplier], [Headcount_Period End],
[Headcount_Posting Type],
 [Headcount_Primary Company
Code - Cost Center], [Headcount_Security ID], [Headcount_Tenure]
, [Headcount_Tenure Order], [Headcount_Work Order ID],
[Headcount_Worker], [Worker ID_HeadCount],
 [Headcount_Worker Pay Type
Name], [Headcount_Worker Type]
 FROM CTE 
 where RankWorkOrderID = 1

Derived Column Transformation:


Data Conversion


Conditional split:


Derived Column:


Union All:


OLEDB Destination:


Mapping:



About Merge and Merge Join Transformations

http://www.learnmsbitutorials.net/ssis-merge-and-mergejoin-example.php

Create Staging Tables in Staging Database and Populate the Staging Tables 8

Download the Sql File attached to the  

Run Script that is attached to create your Staging tables in the Staging Database. Once ran the tables will be made up of these columns.



Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard;

To Load Stg_ReqLocation Table:



Source ReqA:


Source2 Agri Labor Reqs Table:



Sort Transformation:



Sort1:



Merge Join:



Derived Column:


Code:

InsertDate          <add as new column>    GETDATE()          database timestamp [DT_DBTIMESTAMP]           

UpdateDate       <add as new column>    GETDATE()          database timestamp [DT_DBTIMESTAMP]           

RecordFlag          <add as new column>    1                              four-byte signed integer [DT_I4]

Personnel Subarea Code          Replace 'Personnel Subarea Code'                ISNULL([Personnel Subarea Code]) ? "Unknown" : [Personnel Subarea Code]                 Unicode string [DT_WSTR]                           255

Personnel Area Code                      Replace 'Personnel Area Code'                   ISNULL([Personnel Area Code]) ? "Unknown" : [Personnel Area Code]                  Unicode string [DT_WSTR]                           255       

Address 1                            Replace 'Address 1'                          ISNULL([Address 1]) ? "Unknown" : [Address 1]                  Unicode string [DT_WSTR]                           255        

City                        Replace 'City'                      ISNULL(City) ? "Unknown" : City                 Unicode string [DT_WSTR]                           255

State/Province Personnel Subarea Abbreviation                Replace 'State/Province Personnel Subarea Abbreviation'                                ISNULL([State/Province Personnel Subarea Abbreviation]) ? "Unknown" : [State/Province Personnel Subarea Abbreviation]                   Unicode string [DT_WSTR]                           255

ZIP/Postal Code                Replace 'ZIP/Postal Code'                             ISNULL([ZIP/Postal Code]) ? "Unknown" : [ZIP/Postal Code]                    Unicode string [DT_WSTR]                           255         

Personnel Area                 Replace 'Personnel Area'                              ISNULL([Personnel Area]) ? "Unkown" : [Personnel Area]                     Unicode string [DT_WSTR]                           255

Address 2                            Replace 'Address 2'                          ISNULL([Address 2]) ? "Unknown" : [Address 2]                  Unicode string [DT_WSTR]                           255


Conditional Split:


Code:

1 RemoveEmpty LEN([Address 2]) == 0


Derived Column1 :

Code:

Address 2 Replace 'Address 2' "Unknown" Unicode string [DT_WSTR] 255



Union All:


OLEDB Destination:


Mappings:




Repeat the same steps to load all the staging tables. 

To Load Stg_BusinessCode Table:


Source: Alt_Spend Table and choose all columns



Aggregate Transformation:


Derived Column Transformation:


Conditional split:


Derived Column:


Union all:



Destination: Stg_BusinessCode:


Mappings:




To Load Stg_ReqDistributor Table:


Source Vw_ReqA:

SQL command Text:

SELECT [ReqA_Requisition ID], [ReqA_Close Comments], [ReqA_Close Reason], [ReqA_Distribution List],
 [ReqA_# Cancelled]
FROM [dbo].[vw_ReqA]

Source Timing:

SQL command Text:

select distinct  [Job
Posting ID],F8, [Supplier Response],[Selection Process]
 FROM [dbo].[Timing]

Source Resumes:

SQL command Text:

SELECT DISTINCT [Job Posting ID],[Candidate ID], [Security
ID],
 Candidate, [Candidate
First Submit Date], [Candidate Last Interview Date], [Candidate Reject Date],
[Candidate Shortlist Date], 
 [Candidate Status],
[Candidate Submit Date], [Quality Rating], [Reason:], [# Shortlisted]
 FROM [dbo].[Resumes]
 where [Job Posting
ID] is not null

Sort:


Sort 1:


Merge Join:



Sort2:


Sort 3:



Merge Join 1:



Data Conversion:


Derived Column 2 Transformation:

Derived Column 2 code:

GETDATE()

GETDATE()

ISNULL([Job Posting ID]) ? "Unknown" : [Job Posting ID]

ISNULL(F8) ? "Unknown" : F8

ISNULL([ReqA_# Cancelled]) ? 0 : [ReqA_# Cancelled]

ISNULL([ReqA_Close Reason]) ? "Unknown" : [ReqA_Close Reason]

ISNULL([ReqA_Close Comments]) ? "Unknown" : [ReqA_Close Comments]

ISNULL([ReqA_Distribution List]) ? "Unknown" : [ReqA_Distribution List]

ISNULL([Security ID]) ? "Unknown" : [Security ID]

ISNULL([Candidate ID]) ? "Unknown" : [Candidate ID]

ISNULL(Candidate) ? "Unknown" : Candidate

ISNULL([Candidate Status]) ? "Unknown" : [Candidate Status]

ISNULL([Copy of Candidate Submit Date]) ? "Unknown" : [Copy of Candidate Submit Date]

ISNULL([Copy of Candidate Reject Date]) ? "Unknown" : [Copy of Candidate Reject Date]

ISNULL(Reason) ? "Unknown" : Reason

ISNULL([Copy of Candidate Last Interview Date]) ? "Unknown" : [Copy of Candidate Last Interview Date]

ISNULL([Copy of Candidate First Submit Date]) ? " Unknown " : [Copy of Candidate First Submit Date]

ISNULL([Copy of Candidate Shortlist Date]) ? "Unknown" : [Copy of Candidate Shortlist Date]

ISNULL([Copy of # Shortlisted]) ? "0" : [Copy of # Shortlisted]

ISNULL([Supplier Response]) ? 0 : [Supplier Response]

ISNULL([Quality Rating]) ? "Unknown" : [Quality Rating]

ISNULL([Selection Process]) ? 0 : [Selection Process]

Conditional Split Transformation:


Derived Column 1:


Union All Transformation:





OLEDB Destination:



Mappings:



To Load Stg_Headcount (Full Mth) Table:




SQL Command text:

WITH CTE AS
 (
 SELECT  ROW_NUMBER() OVER (PARTITION BY
[Headcount_Work Order ID], [Worker ID_HeadCount]
  ORDER BY[Headcount_Month]
desc ) AS RankWorkOrderID, *
 FROM
[dbo].[vw_HeadCount_FullMth] )
 SELECT [Headcount_Assignment
Length], [Headcount_Diverse Supplier], [Headcount_Period End],
[Headcount_Posting Type],
 [Headcount_Primary Company
Code - Cost Center], [Headcount_Security ID], [Headcount_Tenure]
, [Headcount_Tenure Order], [Headcount_Work Order ID],
[Headcount_Worker], [Worker ID_HeadCount],
 [Headcount_Worker Pay Type
Name], [Headcount_Worker Type]
 FROM CTE 
 where RankWorkOrderID = 1

Derived Column Transformation:


Data Conversion


Conditional split:


Derived Column:


Union All:


OLEDB Destination:


Mapping:



About Merge and Merge Join Transformations

http://www.learnmsbitutorials.net/ssis-merge-and-mergejoin-example.php